package com.examsys.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.examsys.po.Paper;
import com.examsys.po.PaperUserGroup;
import com.examsys.po.UserGroups;
/**
 * 参与考试的用户组数据访问层实现类
 * @author edu-1
 *
 */
public class PaperUserGroupDaoImpl extends AbstractBaseDao<PaperUserGroup, Integer> implements PaperUserGroupDao {

	/**
	 * 添加参与考试的用户组
	 */
	@Override
	public void add(PaperUserGroup obj) throws Exception {
		//构造插入语句
		String sql="Insert into PAPER_USER_GROUP (ID,PAPER_ID,USER_GROUP_ID) values (PAPER_USER_GROUP_ID_SEQ.nextval,?,?)";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getUserGroups().getId()});
	}

	/**
	 * 添加多条参与考试的用户组信息
	 * @param paperUserGroups
	 * @throws Exception
	 */
	public void add(List<PaperUserGroup> paperUserGroups) throws Exception{
		for(PaperUserGroup paperUserGroup:paperUserGroups){
			this.add(paperUserGroup);
		}
	}
	/**
	 * 更新参与考试的用户组
	 */
	@Override
	public void update(PaperUserGroup obj) throws Exception {
		//构造更新语句
		String sql="UPDATE PAPER_USER_GROUP SET PAPER_ID,USER_GROUP_ID WHERE ID=?";
		this.execute(sql, new Object[]{obj.getPaper().getId(),obj.getUserGroups().getId(),obj.getId()});
	}

	/**
	 * 删除参与考试的用户组
	 */
	@Override
	public void delete(Integer id) throws Exception {
		//构建删除语句
		String sql="DELETE FROM PAPER_USER_GROUP WHERE ID=?";
		this.execute(sql, new Object[]{id});
	}

	/**
	 * 获取参与考试的用户组
	 */
	@Override
	public PaperUserGroup get(Integer id) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.USER_GROUP_ID,b.PAPER_NAME,c.GROUP_NAME FROM PAPER_USER_GROUP a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USER_GROUPS c ON a.USER_GROUP_ID=c.ID WHERE a.ID=?";
		Map m = this.uniqueQuery(sql, new Object[]{id});//调用父类的查询方法拿数据
		
		Integer idd = (Integer)m.get("ID");//编号
		Integer user_group_id = (Integer)m.get("USER_GROUP_ID");//用户组编号
		String group_name = (String)m.get("GROUP_NAME");//用户组名称
		
		Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
		String paper_name = (String)m.get("PAPER_NAME");//试卷名称
		
		PaperUserGroup paperUserGroup=new PaperUserGroup();//创建实体类对象
		paperUserGroup.setId(idd);
		
		Paper paper=new Paper();//创建实体类对象
		paper.setId(paper_id);
		paper.setPaper_name(paper_name);
		
		UserGroups userGroups=new UserGroups();//创建实体类对象
		userGroups.setId(user_group_id);
		userGroups.setGroup_name(group_name);
		
		paperUserGroup.setPaper(paper);//设置关联对象
		paperUserGroup.setUserGroups(userGroups);//设置关联对象
		
		return paperUserGroup;
		
	}

	/**
	 * 获取所有参与考试的用户组
	 */
	@Override
	public List<PaperUserGroup> getList() throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.USER_GROUP_ID,b.PAPER_NAME,c.GROUP_NAME FROM PAPER_USER_GROUP a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USER_GROUPS c ON a.USER_GROUP_ID=c.ID";
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<PaperUserGroup> list=new ArrayList<PaperUserGroup>();
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer user_group_id = (Integer)m.get("USER_GROUP_ID");//用户组编号
			String group_name = (String)m.get("GROUP_NAME");//用户组名称
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			PaperUserGroup paperUserGroup=new PaperUserGroup();//创建实体类对象
			paperUserGroup.setId(idd);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			UserGroups userGroups=new UserGroups();//创建实体类对象
			userGroups.setId(user_group_id);
			userGroups.setGroup_name(group_name);
			
			paperUserGroup.setPaper(paper);//设置关联对象
			paperUserGroup.setUserGroups(userGroups);//设置关联对象
			
			list.add(paperUserGroup);
		}
		return list;
	}

	/**
	 * 带条件获取参与考试的用户组
	 */
	@Override
	public List<PaperUserGroup> getList(PaperUserGroup obj) throws Exception {
		//构建查询语句
		String sql="SELECT a.ID,a.PAPER_ID,a.USER_GROUP_ID,b.PAPER_NAME,c.GROUP_NAME FROM PAPER_USER_GROUP a LEFT JOIN PAPER b ON a.PAPER_ID=b.ID LEFT JOIN USER_GROUPS c ON a.USER_GROUP_ID=c.ID WHERE 1=1";
		if(obj!=null){//条件构造
			
			if(obj.getId()!=null&&obj.getId()!=0){
				sql+=" AND a.ID="+obj.getId();
			}
			
			if(obj.getUserGroups()!=null&&obj.getUserGroups().getId()!=null
					&&obj.getUserGroups().getId()!=0){
				sql+=" AND a.USER_GROUP_ID="+obj.getUserGroups().getId();
			}
			
			if(obj.getUserGroups()!=null&&obj.getUserGroups().getGroup_name()!=null
					&&!"".equals(obj.getUserGroups().getGroup_name())){
				sql+=" AND c.GROUP_NAME='"+obj.getUserGroups().getGroup_name()+"'";
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getId()!=null
					&&obj.getPaper().getId()!=0){
				sql+=" AND a.PAPER_ID="+obj.getPaper().getId();
			}
			
			if(obj.getPaper()!=null&&obj.getPaper().getPaper_name()!=null
					&&!"".equals(obj.getPaper().getPaper_name())){
				sql+=" AND b.PAPER_NAME='"+obj.getPaper().getPaper_name()+"'";
			}
		}
		
		List<Map> resultList = this.query(sql, new Object[]{});//调用父类的查询方法拿数据
		List<PaperUserGroup> list=new ArrayList<PaperUserGroup>();
		
		for(Map m:resultList){
			Integer idd = (Integer)m.get("ID");//编号
			Integer user_group_id = (Integer)m.get("USER_GROUP_ID");//用户组编号
			String group_name = (String)m.get("GROUP_NAME");//用户组名称
			
			Integer paper_id = (Integer)m.get("PAPER_ID");//试卷编号
			String paper_name = (String)m.get("PAPER_NAME");//试卷名称
			
			PaperUserGroup paperUserGroup=new PaperUserGroup();//创建实体类对象
			paperUserGroup.setId(idd);
			
			Paper paper=new Paper();//创建实体类对象
			paper.setId(paper_id);
			paper.setPaper_name(paper_name);
			
			UserGroups userGroups=new UserGroups();//创建实体类对象
			userGroups.setId(user_group_id);
			userGroups.setGroup_name(group_name);
			
			paperUserGroup.setPaper(paper);//设置关联对象
			paperUserGroup.setUserGroups(userGroups);//设置关联对象
			
			list.add(paperUserGroup);
		}
		return list;
	}

}
